﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HolterDatabase.Model
{
    public class HolterSet : DatabaseSet
    {
        public override MySqlDataAdapter GetAdapter(MySqlConnection connection)
        {
            var adapter = new MySqlDataAdapter("SELECT * FROM holter, patient where holter.PatientId = patient.Id", connection);

            // Update command
            string updateString = "BEGIN;" +
                                  "UPDATE patient SET patient.FirstName=?FirstName, patient.LastName=?LastName, patient.BirthDate=?BirthDate, patient.Adress=?Adress, patient.Phone=?Phone WHERE patient.Id=?Id;" +
                                  "UPDATE holter SET holter.PatientId=?Id, holter.OutDate=?OutDate, holter.OutTime=?OutTime, holter.PlayDate=?PlayDate, holter.Indication=?Indication, holter.Treatment=?Treatment, holter.Doctor=?Doctor, holter.Comment=?Comment, holter.Conclusion=?Conclusion, holter.Payment=?Payment, holter.Validated=?Validated, holter.Duration=?Duration, holter.ReturnDate=?ReturnDate, holter.ExamNumber=?ExamNumber, holter.Internal=?Internal WHERE holter.Id=?IdHolter;" +
                                  "COMMIT";
            MySqlCommand updateCommand = new MySqlCommand(updateString, connection);
            updateCommand.Parameters.Add("?FirstName", MySqlDbType.VarChar, 256, "FirstName");
            updateCommand.Parameters.Add("?LastName", MySqlDbType.VarChar, 256, "LastName");
            updateCommand.Parameters.Add("?BirthDate", MySqlDbType.Date, 10, "BirthDate");
            updateCommand.Parameters.Add("?Adress", MySqlDbType.VarChar, 256, "Adress");
            updateCommand.Parameters.Add("?Phone", MySqlDbType.VarChar, 20, "Phone");
            updateCommand.Parameters.Add("?OutDate", MySqlDbType.Date, 10, "OutDate");
            updateCommand.Parameters.Add("?OutTime", MySqlDbType.Time, 10, "OutTime");
            updateCommand.Parameters.Add("?PlayDate", MySqlDbType.Date, 10, "PlayDate");
            updateCommand.Parameters.Add("?Indication", MySqlDbType.VarChar, 256, "Indication");
            updateCommand.Parameters.Add("?Treatment", MySqlDbType.VarChar, 256, "Treatment");
            updateCommand.Parameters.Add("?Doctor", MySqlDbType.VarChar, 256, "Doctor");
            updateCommand.Parameters.Add("?Comment", MySqlDbType.VarChar, 1500, "Comment");
            updateCommand.Parameters.Add("?Conclusion", MySqlDbType.VarChar, 1500, "Conclusion");
            updateCommand.Parameters.Add("?Payment", MySqlDbType.Int32, 1, "Payment");
            updateCommand.Parameters.Add("?Validated", MySqlDbType.Int32, 1, "Validated");
            updateCommand.Parameters.Add("?Duration", MySqlDbType.Int32, 3, "Duration");
            updateCommand.Parameters.Add("?ReturnDate", MySqlDbType.Date, 10, "ReturnDate");
            updateCommand.Parameters.Add("?ExamNumber", MySqlDbType.Int32, 10, "ExamNumber");
            updateCommand.Parameters.Add("?Internal", MySqlDbType.Int32, 1, "Internal");

            MySqlParameter parameter = updateCommand.Parameters.Add("?Id", MySqlDbType.Int32, 10, "Id1");
            MySqlParameter parameter2 = updateCommand.Parameters.Add("?IdHolter", MySqlDbType.Int32, 10, "Id");
            parameter.SourceVersion = DataRowVersion.Original;
            adapter.UpdateCommand = updateCommand;

            // Insert Command
            string insertString = "BEGIN;" +
                                  "INSERT INTO patient (FirstName, LastName, BirthDate, Adress, Phone) VALUES (?FirstName, ?LastName, ?BirthDate, ?Adress, ?Phone);" +
                                  "INSERT INTO holter (PatientId, OutDate, PlayDate, OutTime, Indication, Treatment, Doctor, Comment, Conclusion, Payment, Validated, Duration, ReturnDate, ExamNumber, Internal) VALUES (LAST_INSERT_ID(), ?OutDate, ?PlayDate, ?OutTime, ?Indication, ?Treatment, ?Doctor, ?Comment, ?Conclusion, ?Payment, ?Validated, ?Duration, ?ReturnDate, ?ExamNumber, ?Internal);" +
                                  "COMMIT";
            MySqlCommand insertCommand = new MySqlCommand(insertString, connection);
            insertCommand.Parameters.Add("?FirstName", MySqlDbType.VarChar, 256, "FirstName");
            insertCommand.Parameters.Add("?LastName", MySqlDbType.VarChar, 256, "LastName");
            insertCommand.Parameters.Add("?BirthDate", MySqlDbType.Date, 10, "BirthDate");
            insertCommand.Parameters.Add("?Adress", MySqlDbType.VarChar, 256, "Adress");
            insertCommand.Parameters.Add("?Phone", MySqlDbType.VarChar, 20, "Phone");
            insertCommand.Parameters.Add("?OutDate", MySqlDbType.Date, 10, "OutDate");
            insertCommand.Parameters.Add("?OutTime", MySqlDbType.Time, 10, "OutTime");
            insertCommand.Parameters.Add("?PlayDate", MySqlDbType.Date, 10, "PlayDate");
            insertCommand.Parameters.Add("?Indication", MySqlDbType.VarChar, 256, "Indication");
            insertCommand.Parameters.Add("?Treatment", MySqlDbType.VarChar, 256, "Treatment");
            insertCommand.Parameters.Add("?Doctor", MySqlDbType.VarChar, 256, "Doctor");
            insertCommand.Parameters.Add("?Comment", MySqlDbType.VarChar, 1500, "Comment");
            insertCommand.Parameters.Add("?Conclusion", MySqlDbType.VarChar, 1500, "Conclusion");
            insertCommand.Parameters.Add("?Payment", MySqlDbType.Int32, 1, "Payment");
            insertCommand.Parameters.Add("?Validated", MySqlDbType.Int32, 1, "Validated");
            insertCommand.Parameters.Add("?Duration", MySqlDbType.Int32, 3, "Duration");
            insertCommand.Parameters.Add("?ReturnDate", MySqlDbType.Date, 10, "ReturnDate");
            insertCommand.Parameters.Add("?ExamNumber", MySqlDbType.Int32, 10, "ExamNumber");
            insertCommand.Parameters.Add("?Internal", MySqlDbType.Int32, 1, "Internal");
            adapter.InsertCommand = insertCommand;

            // Delete Command
            MySqlCommand deleteCommand = new MySqlCommand("DELETE FROM holter WHERE holter.Id=?Id", connection);
            MySqlParameter delParameter = deleteCommand.Parameters.Add("?Id", MySqlDbType.Int32, 10, "Id");
            delParameter.SourceVersion = DataRowVersion.Original;
            adapter.DeleteCommand = deleteCommand;

            return adapter;
        }
    }
}
